    package com.crysta1.aitrans;

    import android.content.ContentValues;
    import android.content.Context;
    import android.database.Cursor;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteOpenHelper;

    import androidx.annotation.Nullable;

    public class DatabaseHelper extends SQLiteOpenHelper {

        private static final String DATABASE_NAME = "translation.db";
        private static final int DATABASE_VERSION = 3; // 更新数据库版本

        // --- quiz_records 表 ---
        public static final String TABLE_QUIZ_RECORDS = "quiz_records";
        public static final String COLUMN_ID = "id";
        public static final String COLUMN_QUESTION = "question";
        public static final String COLUMN_ANSWER = "answer";
        public static final String COLUMN_TIME_SPENT = "time_spent";
        public static final String COLUMN_SCORE = "score";
        public static final String COLUMN_DIFFICULTY = "difficulty";
        public static final String COLUMN_AI_RESPONSE = "ai_response";
        public static final String COLUMN_TIMESTAMP = "timestamp";

        private static final String CREATE_TABLE_QUIZ_RECORDS =
                "CREATE TABLE " + TABLE_QUIZ_RECORDS + "(" +
                        COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                        COLUMN_QUESTION + " TEXT," +
                        COLUMN_ANSWER + " TEXT," +
                        COLUMN_TIME_SPENT + " INTEGER," +
                        COLUMN_SCORE + " REAL," +
                        COLUMN_DIFFICULTY + " TEXT," +
                        COLUMN_AI_RESPONSE + " TEXT," +
                        COLUMN_TIMESTAMP + " INTEGER" +
                        ")";

        // --- star_state 表 ---
        public static final String TABLE_STAR_STATE = "star_state";
        public static final String COLUMN_STAR_COUNT = "star_count";
        public static final String COLUMN_LAST_PROCESSED_TS = "last_processed_ts";

        private static final String CREATE_TABLE_STAR_STATE =
                "CREATE TABLE " + TABLE_STAR_STATE + "(" +
                        "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                        COLUMN_STAR_COUNT + " INTEGER," +
                        COLUMN_LAST_PROCESSED_TS + " INTEGER" +
                        ")";

        // --- star_state_history 表 ---
        public static final String TABLE_STAR_HISTORY = "star_state_history";
        private static final String CREATE_TABLE_STAR_HISTORY =
                "CREATE TABLE " + TABLE_STAR_HISTORY + "(" +
                        "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                        "action_type TEXT," +          // 改列名
                        "old_star_count INTEGER," +
                        "new_star_count INTEGER," +
                        "delta INTEGER," +
                        "pot_before REAL," +
                        "pot_after REAL," +
                        "reason TEXT," +
                        "timestamp INTEGER" +
                        ")";

        public DatabaseHelper(@Nullable Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(CREATE_TABLE_QUIZ_RECORDS);
            db.execSQL(CREATE_TABLE_STAR_STATE);
            db.execSQL(CREATE_TABLE_STAR_HISTORY);

            // 初始化 star_state 表，默认 0 星，lastProcessedTs = 0
            ContentValues values = new ContentValues();
            values.put(COLUMN_STAR_COUNT, 0);
            values.put(COLUMN_LAST_PROCESSED_TS, 0L);
            db.insert(TABLE_STAR_STATE, null, values);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // 简单做法：删除重建表
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_QUIZ_RECORDS);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_STAR_STATE);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_STAR_HISTORY);
            onCreate(db);
        }

        // --- quiz_records 操作 ---
        public long addQuizRecord(String question, String answer, long timeSpent,
                                  float score, String difficulty, String aiResponse, long timestamp) {

            SQLiteDatabase db = this.getWritableDatabase();
            ContentValues values = new ContentValues();
            values.put(COLUMN_QUESTION, question);
            values.put(COLUMN_ANSWER, answer);
            values.put(COLUMN_TIME_SPENT, timeSpent);
            values.put(COLUMN_SCORE, score);
            values.put(COLUMN_DIFFICULTY, difficulty);
            values.put(COLUMN_AI_RESPONSE, aiResponse);
            values.put(COLUMN_TIMESTAMP, timestamp);

            return db.insert(TABLE_QUIZ_RECORDS, null, values);
        }

        // --- star_state 操作 ---
        public static class StarState {
            public int starCount;
            public long lastProcessedTs;

            public StarState(int starCount, long lastProcessedTs) {
                this.starCount = starCount;
                this.lastProcessedTs = lastProcessedTs;
            }
        }

        public StarState getStarState() {
            SQLiteDatabase db = this.getReadableDatabase();
            Cursor c = db.rawQuery("SELECT " + COLUMN_STAR_COUNT + ", " + COLUMN_LAST_PROCESSED_TS +
                    " FROM " + TABLE_STAR_STATE + " LIMIT 1", null);
            StarState ss = new StarState(0, 0L);
            if (c != null) {
                if (c.moveToFirst()) {
                    ss.starCount = c.getInt(0);
                    ss.lastProcessedTs = c.getLong(1);
                }
                c.close();
            }
            return ss;
        }

        public void saveStarState(int starCount, long lastProcessedTs) {
            SQLiteDatabase db = this.getWritableDatabase();
            ContentValues values = new ContentValues();
            values.put(COLUMN_STAR_COUNT, starCount);
            values.put(COLUMN_LAST_PROCESSED_TS, lastProcessedTs);
            db.update(TABLE_STAR_STATE, values, "id=1", null);
        }

        public void clearStarsAndAdvance(long lastProcessedTs) {
            saveStarState(0, lastProcessedTs);
        }

        // --- star_state_history 操作 ---
        public void addStarHistory(String actionType, int oldStars, int newStars, int delta,
                                   float potBefore, float potAfter, String reason, long timestamp) {
            SQLiteDatabase db = this.getWritableDatabase();
            ContentValues values = new ContentValues();
            values.put("action_type", actionType);
            values.put("old_star_count", oldStars);
            values.put("new_star_count", newStars);
            values.put("delta", delta);
            values.put("pot_before", potBefore);
            values.put("pot_after", potAfter);
            values.put("reason", reason);
            values.put("timestamp", timestamp);
            db.insert(TABLE_STAR_HISTORY, null, values);
        }

        public Cursor getStarHistoryCursor() {
            SQLiteDatabase db = this.getReadableDatabase();
            return db.rawQuery(
                    "SELECT id, action_type, old_star_count, new_star_count, delta, pot_before, pot_after, reason, timestamp " +
                            "FROM " + TABLE_STAR_HISTORY + " ORDER BY timestamp DESC", null);
        }
    }
